﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace MachineBuildingSystem
{
    public class SqlServerHelper
    {
        private string m_ConnectString = "";
        private static SqlServerHelper Instance = null;

        private SqlServerHelper()
        {
            string connectString = ConfigurationManager.AppSettings["SQLConnectionStr"];
            foreach (string item in connectString.Split(';'))
            {
                if (item.StartsWith("Password="))
                    m_ConnectString += "Password=" + CommonHelper.Decrypt(item.Substring("Password=".Length), "tp_ltscm");
                else
                    m_ConnectString += item;
                m_ConnectString += ";";
            }
            m_ConnectString = m_ConnectString.TrimEnd(';');
        }

        public static SqlServerHelper CreateInstance()
        {
            if (Instance == null)
            {
                Instance = new SqlServerHelper();
            }
            return Instance;
        }

        public bool DoSqlExecute(string[] sqlArray)
        {
            SqlConnection conn = null;
            SqlCommand command = null;

            try
            {
                conn = new SqlConnection(m_ConnectString);
                conn.Open();
                SqlTransaction transaction = conn.BeginTransaction();

                command = new SqlCommand();
                command.Connection = conn;
                command.Transaction = transaction;
                for (int i = 0; i < sqlArray.Length; i++)
                {
                    command.CommandText = sqlArray[i];
                    command.ExecuteNonQuery();
                }
                transaction.Commit();
            }
            catch (Exception ex)
            {
                string sqlStr = "";
                foreach (string sql in sqlArray)
                    sqlStr += sql + ",";
                string str = "批量执行SQL：" + sqlStr.TrimEnd(',') + "，数据错误：" + ex.Message;
                LogHelper.error(str);
                throw;
            }
            finally
            {
                conn.Close();
            }
            return true;
        }

        public DataTable DoSqlSelect(string sql)
        {
            DataTable retDT = new DataTable();

            SqlConnection conn = null;
            SqlCommand command = null;
            try
            {
                conn = new SqlConnection(m_ConnectString);
                conn.Open();

                command = new SqlCommand();
                command.CommandText = sql;
                command.Connection = conn;
                DataSet ds = new DataSet();
                SqlDataAdapter sda = new SqlDataAdapter(command);
                sda.Fill(ds);
                retDT = ds.Tables[0];
            }
            catch (Exception ex)
            {
                string str = "执行SQL：" + sql + "，数据错误：" + ex.Message;
                LogHelper.error(str);
                throw;
            }
            finally
            {
                conn.Dispose();
            }
            return retDT;
        }
    }
}
